var SqlNumber = new Class({
	number: NaN,

	initialize: function (number) {
		this.number = this.parse(number);
	},
	
	parse: function (number) {
		throw 'not implemented';
	},
	
	toString: function () {
		return this.number.toString();
	},
	
	valueOf: function () {
		return this.number.valueOf();
	}
});

var SqlInteger = SqlNumber.extend({
	initialize: function (number) {
		this.parent(number);
	},

	parse: function (number) {
		return parseInt(number);
	}
});

var SqlFloat = SqlNumber.extend({
	initialize: function (number) {
		this.parent(number);
	},

	parse: function (number) {
		return parseFloat(number);
	}
});

SqlNumber.prototype.__noSuchMethod__ = function (id, args) {
	SqlNumber.prototype[id] = function () {
		return this.number[id].apply(this.number, arguments);
	};
	return this[id].apply(this, args);
}

function getSqlValue(klazz, value) {
	if (value === undefined || value === null) {
		return 'NULL';
	}
	
	switch (klazz) {
		case String:
			return "'" + value.replace(/'/g, "''") + "'";
		default:
			return value.valueOf();
	}
}

function setSqlValue(klazz, value) {
	if (value === null) {
		return null;
	}
	
	switch (klazz) {
		case Time:
			return new Time({second: value});
		case SqlInteger:
			return new SqlInteger(value);
		case SqlFloat:
			return new SqlFloat(value);
		default:
			return value;
	}
}

function getSqlType(klazz) {
	switch (klazz) {
		case String: 
			return 'varchar(255)';
		case Time:
			return 'INTEGER';
		case SqlInteger:
			return 'INTEGER';
		case SqlFloat:
			return 'FLOAT';
	}
}

var Dao = new Abstract({
	dataPath: system.widgetDataFolder + '/library.db',
	
	$transactionManager: new Class({
		$transactionLevel: 0,
		$rollback: false,
		
		beginTransaction: function () {
			this.$transactionLevel++;
			if (this.$transactionLevel == 1 && !this.$rollback) {
				Dao.instance.exec('BEGIN TRANSACTION;');
			}
		},
		
		endTransaction: function () {
			this.$transactionLevel--;
			if (this.$transactionLevel == 0 && !this.$rollback) {
				Dao.instance.exec('END TRANSACTION;');
			}
		},
		
		rollbackTransaction: function () {
			if (this.$transactionLevel >= 0 && !this.$rollback) {
				this.$rollback = true;
				this.$transactionLevel = -1;
				Dao.instance.exec('ROLLBACK TRANSACTION;');
			}
		},
		
		canCloseDB: function () {
			return this.$transactionLevel <= 0;
		}
		
	}),
	
	getEntityCache: function (klazz) {
		if (!$defined(Dao.$cache)) {
			Dao.$cache = new Hash();
		}
		if (!Dao.$cache.hasKey(klazz.table)) {
			Dao.$cache.set(klazz.table, new Hash());
		}
		return Dao.$cache.get(klazz.table);
	},
	
	clearEntityCache: function () {
		delete Dao.$cache;
	},
		
	get instance () {
		if (!$defined(Dao.$db)) {
			if (!$defined(Dao.$transaction)) {
				Dao.$transaction = new Dao.$transactionManager();
			}
			
			Dao.$db = new SQLite();
			if (!filesystem.itemExists(Dao.dataPath)) {
				Dao.$db.open(Dao.dataPath);
				Dao.$db.exec(Dao.schema);
			} else {
				Dao.$db.open(Dao.dataPath);
			}
		}
		return Dao.$db;
	},
	
	close: function() {
		if ($defined(Dao.$db) && Dao.$transaction.canCloseDB()) {
			Dao.$db.close();
			delete Dao.$db;
			delete Dao.$transaction;
		}
	},
	
	getMaxId: function (table) {
		var maxId = fetchResult(Dao.instance.query("SELECT max(id) + 1 FROM {table}".substitute({'table': table})));
		if (!maxId) {
			return 1;
		} else {
			return maxId;
		}
	},
	
	checkDbVersion: function(entity) {
		if (entity.isSaved()) {
			var dbVersion = fetchResult(Dao.instance.query("SELECT version FROM {table} where id={id}".substitute({table: entity.table, id: entity.id})));
			if (dbVersion && dbVersion != entity.version) {
				throw "optimistic locking exception";
			}
		}
	},

	/**
	 * sqlite currently does not support nested transactions
	 * therefore a counter is used.
	 */

	beginTransaction: function () {
		if (!$defined(Dao.$transaction)) {
			Dao.$transaction = new Dao.$transactionManager();
		}
		Dao.$transaction.beginTransaction();
	},
	
	endTransaction: function () {
		if ($defined(Dao.$transaction)) {
			Dao.$transaction.endTransaction();
		}
	},
	
	rollbackTransaction: function () {
		if ($defined(Dao.$transaction)) {
			Dao.$transaction.rollbackTransaction();
		}
	},
	
	get schema () {
		var sql = "";
		Entity.Entities.each(function (klazz) {
			if (Entity.isAssignableFrom(klazz)) {
				sql += klazz.schema() + "\n";
			}
		});
		return sql;
	},

	$fillEntityByResultRow: function (entity, row) {
		entity.id = row.id;
		entity.version = row.version;
		
		var fields = entity.fields.keys();
		fields.each(function (field) {
			var value = row[field];
			if (!$defined(value)) {
				var objId = row[field+'_id'];
				if (!$defined(objId)) {
					value = null;
				} else {
					value = entity.getFieldClass(field).getEntity(objId);
				}
			}
			if (entity.fields.hasKey(field)) {
				entity.fields.set(field, setSqlValue(entity.getFieldClass(field), value));
			}
		});
	},
	
	getEntity: function (klazz, id) {
		if (Dao.getEntityCache(klazz).hasKey(id)) {
			return Dao.getEntityCache(klazz).get(id).clone();
		} else {			
			try {
				var entity = new klazz();
				var sqlResult = Dao.instance.query('SELECT * FROM {table} WHERE id={id}'.substitute({table: entity.table, id: id}));
				var result = sqlResult.getAll();
				sqlResult.dispose();
				
				if (result.length == 0) {
					entity = null;
				} else {
					var row = result[0];
					Dao.$fillEntityByResultRow(entity, row);
					Dao.getEntityCache(entity.class).set(id, entity.clone());
				}
			} finally {
				Dao.close();			
			}
			return entity;
		}
	},
	
	saveOrUpdate: function(entity, cascadeUpdate) {
		cascadeUpdate = $pick(cascadeUpdate, false);		
		var fieldsToUpdate = new Hash();
		var fieldsToSaveOrUpdate = new Hash();

		try {
			Dao.checkDbVersion(entity);
			entity.fields.each(function (value, key) {
				if (Entity.isAssignableFrom(entity.getFieldClass(key))) {
					if (value != null && cascadeUpdate) {
						Dao.checkDbVersion(value);
					}
					fieldsToSaveOrUpdate.set(key, value);
				} else {
					fieldsToUpdate.set(key, getSqlValue(entity.getFieldClass(key), value));
				}
			});
		
			Dao.beginTransaction();
			
			fieldsToSaveOrUpdate.each(function (value, key) {
				if (!$defined(value)) {
					return;
				}
				
				if (!value.isSaved() || cascadeUpdate) {
					Dao.saveOrUpdate(value, entity.getFieldClass(key), cascadeUpdate);
				}
			});
			
			fieldsToSaveOrUpdate.keys().each(function (key) {
				var value = fieldsToSaveOrUpdate.get(key);
				fieldsToUpdate.set(key + "_id", value != null ? value.id : "'NULL'");
			});
	
			entity.incrementVersion();
			if (!entity.isSaved()) {
				var id = Dao.getMaxId(entity.table);
				var sql = "INSERT INTO {table}(id, version, {columns}) VALUES ({id}, {version}, {values});".substitute(
					{
						table: entity.table, 
						columns: fieldsToUpdate.keys().join(","), 
						id: id, 
						version: entity.version, 
						values: fieldsToUpdate.values().join(",")
					}); 
				Dao.instance.exec(sql);
				entity.id = id;
			} else {
				var fieldUpdate = "";
				fieldsToUpdate.each(function (value, key) {
					fieldUpdate += "," + key + "=" + value;
				});
				var sql = "UPDATE {table} SET version={version} {updates} WHERE id={id};".substitute({
					table: entity.table, 
					version: entity.version, 
					updates: fieldUpdate, 
					id: entity.id}); 
				Dao.instance.exec(sql);
			}
			Dao.endTransaction();
			
			if (cascadeUpdate) {
				Dao.getEntityCache(entity.class).set(entity.id, entity.clone());
			} else {
				var cached;
				if (Dao.getEntityCache(entity.class).hasKey(entity.id)) {
					cached = Dao.getEntityCache(entity.class).get(entity.id);
				} else {
					cached = new entity.class();
				}
				entity.fields.each(function (value, key) {
					if (!Entity.isAssignableFrom(entity.getFieldClass(key))) {
						cached[key] = value;
					}
				});
			}
		} catch (e) {
			log('error occured, transaction rolled back.');
			Dao.rollbackTransaction();
			throw e;
		} finally {
			Dao.close();
		}
	},
	
	delete: function (entity) {
		var sql = "DELETE FROM {table} WHERE id={id}".substitute({table: entity.table, id: entity.id});
		if (entity.isSaved()) {
			Dao.instance.exec(sql);
			var rslt = Dao.instance.numRowsAffected == 1;
			Dao.close();
			Dao.getEntityCache(entity.class).remove(entity.id);
			return rslt;
		} else {
			return false;
		}
	},
	
	getEntitiesByFieldsValue: function (entity) {
		var fieldsOfValue = new Hash();
		
		entity.fields.each(function (value, key) {
			if ($defined(value)) {
				if (Entity.isInstance(value)) {
					fieldsOfValue.set(key+'_id', value.id);
				} else {
					fieldsOfValue.set(key, getSqlValue(entity.getFieldClass(key), value));
				}
			}
		});
		
		if (fieldsOfValue.length != 0) {
			var criteria = "";
			fieldsOfValue.each(function (value, key) {
				criteria += key + "=" + value + " AND ";
			});
			criteria = criteria.substring(0, criteria.length - 5);
			var result = Dao.instance.query("SELECT * FROM {table} WHERE {criteria}".substitute({table: entity.table, criteria: criteria}));
			var all = result.getAll();
			result.dispose();
			
			var entities = [];
			all.each(function (row) {
				var e = new entity.class();
				Dao.$fillEntityByResultRow(e, row);
				Dao.getEntityCache(e.class).set(e.id, e.clone());
				entities.push(e);
			});
			return entities;
		} else {
			return [];
		}
	},
	
	getAllEntities: function (klazz, whereClause, orderby) {
		var selectionCritieria = $defined(whereClause) ? ("WHERE " + whereClause) : "";
		
		orderby = $pick(orderby, {});
		var orderbyClause = "";
		for (prop in orderby) {
			if (klazz.fieldClasses.hasKey(prop)
					&& !Entity.isAssignableFrom(klazz.fieldClasses.get(prop))) {
				orderbyClause += (prop + " ") + (orderby[prop] ? "ASC" : "DESC");
			}
		}
		if (orderbyClause != "") {
			orderbyClause = "ORDER BY " + orderbyClause;
		}
		
		var result = Dao.instance.query("SELECT * FROM {table} {critieria} {orderby};".substitute({table: klazz.table, critieria: selectionCritieria, orderby: orderbyClause}));
		var all = result.getAll();
		result.dispose();
		
		var entities = [];
		all.each(function (row) {
			var entity = new klazz();
			Dao.$fillEntityByResultRow(entity, row);
			Dao.getEntityCache(entity.class).set(entity.id, entity.clone());
			entities.push(entity);
		});
		return entities;
	}
});

var Entity = new Class({
	
	initialize: function (attributeMap) {
		this.id = 0;
		this.version = 0;
		this.$fields = new Hash();
		attributeMap = $pick(attributeMap, {});
		
		this.class.fieldClasses.keys().each(function (field) {
			this.set(field, attributeMap[field]);
		}.bind(this));
	},
	
	incrementVersion: function () {
		this.version++;
	},
	
	get fields function () {
		return this.$fields;
	},
	
	getFieldClass: function (field) {
		return this.class.fieldClasses.get(field);
	},
	
	get table function() {
		return this.class.table;
	},
	
	get: function (field) {
		return this.$fields.get(field);
	},
	
	set: function (field, value) {
		if (this.class.fieldClasses.hasKey(field)) {
			this.$fields.set(field, value);
		} else {
			throw "attribute does not exist.";
		}
	},
	
	$getAssociations: function (klazz, belongsTo) {
		var criteria = new klazz();
		criteria[belongsTo] = new this.class();
		criteria[belongsTo].id = this.id;
		return Dao.getEntitiesByFieldsValue(criteria);
	},
	
	isSaved: function () {
		return this.id != 0;
	},
	
	saveOrUpdate: function (cascadeUpdate) {
		return Dao.saveOrUpdate(this, cascadeUpdate);
	},
	
	delete: function() {
		return Dao.delete(this);
	},
	
	equals: function (other) {
		if (!Entity.isInstance(other)) {
			return false;
		}
		
		var keys = this.$fields.keys();
		keys.sort();
		var otherKeys = other.$fields.keys();
		otherKeys.sort();
		if (!keys.same(otherKeys)) {
			return false;
		}
		
		return !keys.some(function (key) {
			var value = this.$fields.get(key);
			var otherValue = other.$fields.get(key);
			if (value != null && $type(value.equals) == 'function') {
				return !value.equals(otherValue);
			} else {
				return value != otherValue;
			}
		}.bind(this));
	},
	
	clone: function () {
		var theClone = new this.class();
		theClone.id = this.id;
		theClone.version = this.version;
		this.fields.each(function (value, key) {
			theClone[key] = value;
		});
		return theClone;
	}
});

function convertToAttributeArray(attributeMaps) {
	var type = $type(attributeMaps);
	if (type != 'array' && type != 'object') {
		throw 'invalid arguments for creation';
	}
	if (type != 'array') {
		attributeMaps = [attributeMaps];
	}
	return attributeMaps;
}

const CONSTRAINT_NOT_NULL = "NOT_NULL";
const CONSTRAINT_UNIQUE = "UNIQUE";
const VALID_CONSTRAINTS = [CONSTRAINT_NOT_NULL, CONSTRAINT_UNIQUE];

var $entityClassExtension = {
	schema: function  () {
		var sql = "CREATE TABLE IF NOT EXISTS `{table}` ("+
				  " `id` INTEGER PRIMARY KEY," +
				  " `version` INTEGER NOT NULL" +
				  " {columns});";
		var columnSql = "";
		this.fieldClasses.each(function (klazz, field) {
			if (Entity.isAssignableFrom(klazz)) {
				columnSql += ', `{field}_id` INTEGER {not_null} CONSTRAINT `fk_{table}_{field_table}` REFERENCES `{field_table}`(`id`)'.substitute(
					{
					field: field, 
					not_null: this.fieldNotNull(field) ? 'NOT NULL' : '', 
					table: this.table, 
					field_table: klazz.table});
			} else {
				columnSql += ', `{field}` {type} {not_null}'.substitute({
				field: field, 
				type: getSqlType(klazz), 
				not_null: this.fieldNotNull(field) ? 'NOT NULL' : ''});
			}
		}.bind(this));
		
		var unqiueFields = [];
		this.fieldClasses.each(function (klazz, field) {
			if (this.fieldUnique(field)) {
				unqiueFields.push(
					Entity.isAssignableFrom(klazz) ? field + "_id" : field);
			}
		}.bind(this));
		if (unqiueFields.length > 0) {
			columnSql += ', UNIQUE(' + unqiueFields.join(',') + ')';
		}
		
		return sql.substitute({table: this.table, columns: columnSql});
	},
	
	setConstraint: function (field, constraint) {
		if ($type(constraint) != 'Array') {
			constraint = [constraint];
		}
		constraint = constraint.filter(function (item) {
			return VALID_CONSTRAINTS.contains(item);
		});
		var existing = $pick(this.$fieldConstraints.get(field), []);
		this.$fieldConstraints.set(field, constraint.merge(existing));
	},
	
	fieldNotNull: function (field) {
		return $pick(this.$fieldConstraints.get(field), []).contains(CONSTRAINT_NOT_NULL);
	},
	
	fieldUnique: function (field) {
		return $pick(this.$fieldConstraints.get(field), []).contains(CONSTRAINT_UNIQUE);
	},
	
	getEntity: function (id) {
		return Dao.getEntity(this, id);
	},
	
	getAllEntities: function (whereClause, orderby) {
		return Dao.getAllEntities(this, whereClause, orderby);
	},
	
	create: function () {
		var entities = [];
		try {
			Dao.beginTransaction();
			$A(arguments).each(function (attributes) {
				try {
					var entity = new this(attributes);
					entity.saveOrUpdate();
					entities.push(entity);
				} catch (e) {
					log(e.errMsg);
					throw e;
				}
			}.bind(this));
			Dao.endTransaction();
		} catch (e) {
			Dao.rollbackTransaction();
			throw e;
		} finally {
			Dao.close();
		}
		return entities.length == 1 ? entities[0] : entities;
	},
	
	find: function () {
		var entities = [];
		$A(arguments).each(function (attributes) {
			try {
				var found = Dao.getEntitiesByFieldsValue(new this(attributes));
				if (found) {
					entities.extend(found);
				}
			} catch (e) {
				log(e.errMsg);
			}
		}.bind(this));
		entities.unique(function (entity1, entity2) {
			return entity1.id == entity2.id;
		});
		return entities;
	},
	
	createOrFind: function () {
		var entities = [];
		try {
			Dao.beginTransaction();
			$A(arguments).each(function (attributes) {
				var rslt = this.find(attributes);
				if (rslt.length == 0) {
					entities.push(this.create(attributes));
				} else {
					entities.extend(rslt);
				}
			}.bind(this));
			Dao.endTransaction();
		} catch (e) {
			Dao.rollbackTransaction();
			throw e;
		} finally {
			Dao.close();
		}
		entities.unique(function (entity1, entity2) {
			return entity1.id == entity2.id;
		});
		return  entities.length == 1 ? entities[0] : entities;
	},
	
	hasOne: function(field, klazz, belongsTo) {
		this.defineGetter(field, function () {
			if (!this.isSaved) {
				return undefined;
			}

			if (!$defined(this['$' + field])) {
				this['$' + field] = this.$getAssociations(klazz, belongsTo)[0];
			}
			return this['$' + field];
		});
	},
	
	hasMany: function(field, klazz, belongsTo) {
		this.defineGetter(field, function () {
			if (!this.isSaved) {
				return undefined;
			}
		
			if (!$defined(this['$' + field])) {
				this['$' + field] = this.$getAssociations(klazz, belongsTo);
			}
			return this['$' + field];
		});
	}
};

$extend(Entity, {
	Entities: [],

	extend: function (table, fields, proto) {
		var extendedClass = Class.prototype.extend.call(Entity, proto);
		extendedClass.$fieldClasses = new Hash();
		extendedClass.$fieldConstraints = new Hash();
		extendedClass.$table = table;
		$extend(extendedClass, $entityClassExtension);
		extendedClass.__defineGetter__('table', function () {
			return this.$table;
		});
		extendedClass.__defineGetter__('fieldClasses', function () {
			return this.$fieldClasses;
		});
		extendedClass.prototype.__defineGetter__('class', function () {
			return extendedClass;
		});
		
		if (!Entity.Entities.some(function (klazz) {
			return klazz.table == table;
		})) {
			Entity.Entities.push(extendedClass);			
		}
		
		for (var field in fields) {
			Entity.$addField.bind(extendedClass)(field, fields[field]);
		}
		return extendedClass;
	},
	
	$addField: function (field, klazz) {
		this.$fieldClasses.set(field, klazz);
		
		this.defineGetter(field, function () {
			return this.get(field);
		});
		this.defineSetter(field, function (val) {
			if (klazz.prototype.isPrototypeOf(val) 
					|| $type(val) == klazz.name.toLowerCase()
					|| !$defined(val)) {
				this.set(field, val);
			} else {
				throw "invalid value";
			}
		});
	}
});
